﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqLiteHelper;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Dwg_output.DCSTag
{
    class Wire_DcsTagToExcel
    {
        private string _Path;
        private string _PathExcel;
        private string _PathDb;
        public Wire_DcsTagToExcel(string pathExcel, string pathDb)
        {
            _PathExcel = pathExcel;
            _PathDb = pathDb + "\\项目.db";

        }


        private bool isFileExDb()
        {
            if (File.Exists(_PathExcel))
                return true;
            else
                return false;

        }
        public int writeExceltoSqlite()
        {
            if (false == isFileExDb())
            {
                MessageBox.Show("选择的导入表格丢失丢失");
                return 0;
            }

            //新建数据库
            SQLiteHelper buildDB = new SQLiteHelper(_PathDb, "");

            string deleteBIAO = @"DROP TABLE IF EXISTS device";                             
                                   

           
            string device =@"CREATE TABLE device(id INTEGER PRIMARY KEY, cabinet_id INTEGER NOT NULL, model TEXT, name TEXT, kind TEXT
                                   , rack_serial INTEGER, area_id INTEGER, control_id INTEGER, node_addr INTEGER, rack_addr INTEGER, card_addr INTEGER, redun INTEGER
                                   , position TEXT, x REAL, y REAL, z REAL, angle REAL, x_len TEXT, y_len TEXT, kind_dif TEXT
                                   , kk_type TEXT, next_power_info TEXT, sign_flow TEXT, power_num TEXT, type_wire TEXT, ter_pair TEXT, Aircurit_type TEXT, pow_kind TEXT)";
            


            buildDB.buildEXcel(deleteBIAO);
        
            int error1 = buildDB.addDate(device);
            return error1;

        }


        public int Read_DCStag_device(string _PathDb)
        {
            int int_cabinet_ID;
            int resSave2;

            using (FileStream fs = File.Open(_PathExcel, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))//打开文件
            {
                ////获取表格防止额外溢出，注意每个单元格的值
                IWorkbook wk = ExcelHelper.ReadFromExcel(_PathExcel);
                ISheet sheet = wk.GetSheet("系统模块排布");

                // 连接数据库
                SQLiteHelper sqlhelper = new SQLiteHelper(_PathDb, ""); //参数1: db的绝对路径 参数2: 数据库密码
                string sql_inst = @"INSERT INTO device (cabinet_id, model,name,kind,control_id,rack_addr,card_addr,node_addr) 
                                    VALUES  (@cabinet_id, @model,@name,@kind,@control_id,@rack_addr,@card_addr,@node_addr)";

                IFormulaEvaluator evaluator = wk.GetCreationHelper().CreateFormulaEvaluator();//放在循环里面可以大幅增加读取时间O。0

                string searchText = "工段";
                string currrnt_cabinet = string.Empty;
                int cabinet_id = 0;
               
                int rack_addr = 1;
               
                List<string> error_model_list = new List<string>();

                for (int r = 0; r <= sheet.LastRowNum; r++)
                {
                    //行为空则跳过
                    IRow ccurrentRow = sheet.GetRow(r);
                    if (ccurrentRow == null || ccurrentRow.ToString() == "")
                    { continue; }

                    //获取当前行的第一个单元格
                    ICell currrentCell = ccurrentRow.GetCell(0);
                    if (currrentCell == null || currrentCell.ToString() == "")
                    { continue; }


                    if (currrentCell.StringCellValue.Equals(searchText) )//操作台标志位
                    {
                        //存入机柜
                        IRow row_cabinet = sheet.GetRow(r+2);
                        ICell current_jigui_Cell = row_cabinet.GetCell(1);
                        if (current_jigui_Cell == null)
                        {
                            continue;
                        }
                        string current_jigui = current_jigui_Cell.StringCellValue;
                        if (!string.IsNullOrEmpty("current_jigui"))
                        {

                            SQLiteParameter[] param_Tag1 = new SQLiteParameter[]{
                                       new SQLiteParameter("@cabinet_id", cabinet_id),
                                       new SQLiteParameter("@model", "T8-SYS-A1"),
                                        new SQLiteParameter("@name", current_jigui),
                                       new SQLiteParameter("@control_id",-1 ),
                                       new SQLiteParameter("@rack_addr",-1),
                                       new SQLiteParameter("@card_addr",-1),
                                       new SQLiteParameter("@node_addr",-1),
                                       new SQLiteParameter("@kind","机柜"),
                                     };
                            //保存到数据库执行
                            resSave2 = sqlhelper.addDate(sql_inst, param_Tag1);
                          
                            rack_addr = 1;
                        }


                        //机架
                        for (int i = 0; i < 8; i++)
                        {
                            ICell jijia_Cell = row_cabinet.GetCell(2+(i*4));
                            if (jijia_Cell== null || string.IsNullOrEmpty(jijia_Cell.StringCellValue))
                            {
                                continue;
                            }
                            string jijia = jijia_Cell.StringCellValue;
                            if (!string.IsNullOrEmpty("jijia"))
                            {
                                string model_name;
                                if (jijia.Contains("Rack911"))
                                {
                                    model_name = "Rack911";
                                }
                                else if (jijia.Contains("Rack901"))
                                {
                                    model_name = "Rack901";
                                }
                                else
                                {
                                    model_name = "";
                                }

                                SQLiteParameter[] param_Tag1 = new SQLiteParameter[]{
                                       new SQLiteParameter("@cabinet_id", cabinet_id),
                                       new SQLiteParameter("@model",model_name ),
                                        new SQLiteParameter("@name", "RACK"+rack_addr),
                                       new SQLiteParameter("@control_id",rack_addr ),
                                       new SQLiteParameter("@rack_addr",rack_addr),
                                       new SQLiteParameter("@card_addr",-1),
                                        new SQLiteParameter("@node_addr",-1),
                                        new SQLiteParameter("@kind","机架"),

                                     };
                                //保存到数据库执行
                                resSave2 = sqlhelper.addDate(sql_inst, param_Tag1);

                            }

                            int tag = 1;
                            //卡件基座
                            for (int y = 0; y < 10; y++)
                            {
                                
                                IRow row_tag = sheet.GetRow(r + 2+y);
                                for (int x = 0; x < 3; x++)
                                {


                                    if (x == 0)
                                    {
                                        ICell yag_Cell = row_tag.GetCell(3 + (i * 4) + x);
                                        if (yag_Cell == null || string.IsNullOrEmpty(yag_Cell.StringCellValue))
                                        {
                                            continue;
                                        }
                                        string tagModel = yag_Cell.StringCellValue;


                                        string card_name = "MB" + rack_addr + "-" + (y+1);


                                        SQLiteParameter[] param_Tag1 = new SQLiteParameter[]{
                                        new SQLiteParameter("@cabinet_id", cabinet_id),
                                        new SQLiteParameter("@model",tagModel ),
                                        new SQLiteParameter("@name", card_name),
                                        new SQLiteParameter("@control_id",rack_addr ),
                                        new SQLiteParameter("@rack_addr",rack_addr),
                                        new SQLiteParameter("@card_addr",-1),
                                        new SQLiteParameter("@kind","基座"),
                                        new SQLiteParameter("@node_addr",y),

                                        };
                                        resSave2 = sqlhelper.addDate(sql_inst, param_Tag1);

                                    }
                                    else
                                    {
                                        ICell yag_Cell = row_tag.GetCell(3 + (i * 4) + x);
                                        if (yag_Cell == null || string.IsNullOrEmpty(yag_Cell.StringCellValue))
                                        {
                                            tag++;
                                            continue;
                                        }

                                        string tagModel = yag_Cell.StringCellValue;

                                        string card_name = "R" + rack_addr + "-" + tag;
                                      
                                        SQLiteParameter[] param_Tag1 = new SQLiteParameter[]{
                                        new SQLiteParameter("@cabinet_id", cabinet_id),
                                        new SQLiteParameter("@model",tagModel ),
                                        new SQLiteParameter("@name", card_name),
                                        new SQLiteParameter("@control_id",rack_addr ),
                                        new SQLiteParameter("@rack_addr",rack_addr),
                                        new SQLiteParameter("@card_addr",tag),
                                        new SQLiteParameter("@kind","卡件"),
                                          new SQLiteParameter("@node_addr",y),
                                        };

                                        //保存到数据库执行
                                        resSave2 = sqlhelper.addDate(sql_inst, param_Tag1);

                                        tag++;


                                    }
                                  

                                }

                            }


                            rack_addr++;//机架加1
                        }
                        cabinet_id++;

                    }

                  
                }

                return 1;
            }
        }



    }
    public class ExcelHelper
    {
        /// <summary>
        /// 从Excel读取数据，只支持单表
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static IWorkbook ReadFromExcel(string FilePath)
        {
            IWorkbook wk = null;
            try
            {

                string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名

                using (FileStream fs = File.OpenRead(FilePath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                //构建DataTable
                IRow row = sheet.GetRow(0);
                return wk;
            }
            catch (Exception x)
            {

                MessageBox.Show(x.ToString());
            }

            return wk;


        }
    }
}
